﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;

namespace DataBaseManger
{
    public abstract class DataBaseHelper:IDataBaseHelper
    {
        #region IDataBaseHelper Members
        protected string _provideName;
        protected DbConnection _connection = null;
        protected DbTransaction _transaction = null;

        private static Dictionary<string, DbProviderFactory> _dataFactorys = new Dictionary<string, DbProviderFactory>();

        //public DataBaseHelper()
        //{ }

        //public DataBaseHelper()
        //{ 
        //}

        protected abstract DbConnection CreateConnection();

        public void Open()
        {
            _connection = CreateConnection();
            try
            {
                if (_connection != null && _connection.State == ConnectionState.Closed)
                    _connection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Close()
        {
            try
            {
                if (_connection != null && _connection.State == ConnectionState.Open)
                    _connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbProviderFactory GetDataFactory()
        {
            if (!_dataFactorys.ContainsKey(_provideName))
            {
                DbProviderFactory dataFactory = DbProviderFactories.GetFactory(_provideName);
                _dataFactorys.Add(_provideName, dataFactory);
            }
            return _dataFactorys[_provideName];
        }

        public int ExecuteNonQuery(string sSQL)
        {
            return ExecuteNonQuery(sSQL, CommandType.Text, null);
        }

        public int ExecuteNonQuery(string sSQL, CommandType cmdType)
        {
            return ExecuteNonQuery(sSQL, cmdType, null);
        }

        public int ExecuteNonQuery(string sSQL, CommandType cmdType,params DbParameter[] cmdPara)
        {
            using (DbCommand command = _connection.CreateCommand())
            {
                command.CommandType = cmdType;
                command.CommandText = sSQL;
                SetCommandParameters(command, cmdPara);
                if (_transaction != null)
                {
                    command.Transaction = _transaction;
                }
                return command.ExecuteNonQuery();
            }
        }

        public DataSet ExecuteQuery(string sSQL)
        {
            return ExecuteQuery(sSQL, CommandType.Text, null);
        }

        public DataSet ExecuteQuery(string sSQL, CommandType cmdType)
        {
            return ExecuteQuery(sSQL, cmdType, null);
        }

        public DataSet ExecuteQuery( string sSQL,CommandType cmdType, params DbParameter[] cmdPara)
        {
            DataSet oReturnDS = null;
            using (DbCommand command=_connection.CreateCommand())
            {
                DbDataAdapter adapter = GetDataFactory().CreateDataAdapter();
                command.CommandText = sSQL;
                command.CommandType = cmdType;
                if (_transaction != null)
                {
                    command.Transaction = _transaction;
                }
                SetCommandParameters(command, cmdPara);
                adapter.SelectCommand = command;
                oReturnDS = new DataSet();
                adapter.Fill(oReturnDS);
                command.Parameters.Clear();
            }
            return oReturnDS;
        }

        public object ExecuteScalar(string sSQL)
        {
            return ExecuteScalar(sSQL, CommandType.Text);
        }

        public object ExecuteScalar(string sSQL, CommandType cmdType)
        {
            return ExecuteScalar(sSQL, cmdType, null);
        }

        public object ExecuteScalar(string sSQL, CommandType cmdType,params DbParameter[] cmdPara)
        {
            object obj = null;
            using (DbCommand command=_connection.CreateCommand())
            {
                command.CommandText = sSQL;
                SetCommandParameters(command,cmdPara);
                command.CommandType = cmdType;
                if (_transaction != null)
                {
                    command.Transaction = _transaction;
                }
                obj = command.ExecuteScalar();
                command.Parameters.Clear();
            }
            return obj;
        }

        public DbDataReader ExecuteReader(string sSQL)
        {
            return ExecuteReader(sSQL, CommandType.Text,null);
        }

        public DbDataReader ExecuteReader(string sSQL,CommandType cmdType)
        {
            return ExecuteReader(sSQL, cmdType, null);
        }

        public DbDataReader ExecuteReader(string sSQL, CommandType cmdType,params DbParameter[] cmdPara)
        {
            DbCommand command = _connection.CreateCommand();
            SetCommandParameters(command,cmdPara);
            DbDataReader DbRader = command.ExecuteReader(CommandBehavior.CloseConnection);
            command.Parameters.Clear();
            return DbRader;
        }

        private void SetCommandParameters(DbCommand cmd,DbParameter[] cmdParms)
        {
            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        #endregion
    }
}
